{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "399a0b7c",
   "metadata": {},
   "source": [
    "该文件主要是为了生成需要的数据，并将数据保存到 data.db 中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a7fc6785",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import string\n",
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e4b0a1d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据库地址：数据库放在上一级目录下\n",
    "db_path = os.path.join(os.path.dirname(os.getcwd()),\"data.db\")\n",
    "engine_path = \"sqlite:///\" + db_path\n",
    "# 创建数据库引擎\n",
    "engine = create_engine(engine_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f8f2d2b",
   "metadata": {},
   "source": [
    "### 省市区编码与经纬度对应数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0363ff90",
   "metadata": {},
   "outputs": [],
   "source": [
    "adcode_lng_lat_df = pd.read_excel(\"./省市区adcode与经纬度映射表.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "002fc2ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "adcode_lng_lat_df.to_sql(\"adocde_lng_lat\", engine, if_exists='replace', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82e13251",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b76b6f5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d4b45f08",
   "metadata": {},
   "source": [
    "### 商品退单率数据\n",
    "> 使用章节：第五章\n",
    ">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1290dc92",
   "metadata": {},
   "outputs": [],
   "source": [
    "shop_refuse_df = pd.DataFrame(\n",
    "    data={\n",
    "        \"shopid\":[n for e in [[i]*56 for i in range(1,81)] for n in e],\n",
    "        \"create_time\":pd.date_range(\"2022-01-03\", periods=56, freq=\"D\").tolist()*80,\n",
    "        \"total_num\":np.random.randint(100,300,80*56),\n",
    "        \"td_num\":np.random.randint(10,30,80*56)\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6849f659",
   "metadata": {},
   "outputs": [],
   "source": [
    "shop_refuse_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "022770d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "shop_refuse_df.to_sql(\"shopRefuse\", engine, if_exists='replace', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d03852fb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ffa67065",
   "metadata": {},
   "source": [
    "### 用户商品交易数据\n",
    ">使用章节：第三章\n",
    ">\n",
    "> 适用于RFM模型\n",
    ">\n",
    ">user_id（用户id）、create_time（创建时间）、order_id（商品id）、amount（交易金额）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cdb0ef0",
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_df = pd.read_csv(\"./sale.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a95b8b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a8136cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 替换时间为 2020 年\n",
    "sale_df[\"create_time\"] = sale_df[\"create_time\"].map(\n",
    "    lambda x:x.replace(\"2018\",\"2021\") if pd.notnull(x) else x\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81b0f17a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "762aa4e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_df.to_sql(\"business\", engine, if_exists='replace', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0896e49",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "43986657",
   "metadata": {},
   "source": [
    "### 用户数据\n",
    ">与comment表连用可以用于舆情分析\n",
    ">\n",
    "> 保存到数据库 users 表中\n",
    ">\n",
    "> user_id（用户id）、username（用户名）、age（年龄）\n",
    ">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9d6ae89e",
   "metadata": {},
   "outputs": [],
   "source": [
    "name_str_list = [i for i in string.ascii_letters+string.digits]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ce1302a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "mobile_idcard_list = [i for i in string.digits]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "787c95fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['0', '1', '2', '3', '4', '5', '6', '7', '8', '9']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mobile_idcard_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8a06e9b1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['195758246553848289',\n",
       " '472714950764659645',\n",
       " '523421536400509351',\n",
       " '219743059598612004',\n",
       " '901458600325975590',\n",
       " '345699901136224909',\n",
       " '006310583953251459',\n",
       " '065856777095878441',\n",
       " '709307135707601571',\n",
       " '728038742201258279']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[\"\".join(np.random.choice([i for i in mobile_idcard_list],18)) for _ in range(10)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6bb488ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 随机生成10000个用户的信息\n",
    "user_df = pd.DataFrame(data={\n",
    "    \"user_id\":range(1,10001),\n",
    "    \"username\":[\"\".join(np.random.choice(name_str_list,6)) for _ in range(10000)],\n",
    "    \"age\":np.random.choice(range(20,50),10000),\n",
    "    \"mobile\":[\"\".join(np.random.choice([i for i in mobile_idcard_list],11)) for _ in range(10000)],\n",
    "    \"idcard\":[\"\".join(np.random.choice([i for i in mobile_idcard_list],18)) for _ in range(10000)]\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d6a33817",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>username</th>\n",
       "      <th>age</th>\n",
       "      <th>mobile</th>\n",
       "      <th>idcard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>773</th>\n",
       "      <td>774</td>\n",
       "      <td>x90T5D</td>\n",
       "      <td>21</td>\n",
       "      <td>49023978745</td>\n",
       "      <td>363970377588150599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6806</th>\n",
       "      <td>6807</td>\n",
       "      <td>r4vqOh</td>\n",
       "      <td>46</td>\n",
       "      <td>62543752593</td>\n",
       "      <td>128120646411550312</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_id username  age       mobile              idcard\n",
       "773       774   x90T5D   21  49023978745  363970377588150599\n",
       "6806     6807   r4vqOh   46  62543752593  128120646411550312"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_df.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "daf5f7a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "user_df.to_sql(\"users\", engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "211c481b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "1f7ca370",
   "metadata": {},
   "source": [
    "### 商品评价数据\n",
    ">使用章节：第四章\n",
    ">\n",
    "> 商品评论数据来自 [阿里云天池比赛](https://tianchi.aliyun.com/competition/entrance/531890/introduction)\n",
    ">\n",
    "> 数据保存到数据库 comment 表中\n",
    ">\n",
    ">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c824211",
   "metadata": {},
   "outputs": [],
   "source": [
    "comment_df = pd.read_csv(\"./earphone_sentiment.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c39870b",
   "metadata": {},
   "outputs": [],
   "source": [
    "comment_df.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a949d2bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "comment_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fbf847e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加创建时间\n",
    "index = pd.date_range(\"2021-01-01\", \"2021-12-31\")\n",
    "\n",
    "# 随机选择 17176 条时间\n",
    "comment_df[\"create_time\"] = np.random.choice(a=index,size=17176)\n",
    "\n",
    "# 添加用户id\n",
    "comment_df[\"user_id\"] = range(1,len(comment_df)+1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a47df1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "comment_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d16db7cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "comment_df.to_sql(\"comment\", engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bee7d0d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "5a810391",
   "metadata": {},
   "source": [
    "### 观研报告网数据\n",
    "> 观研报告网：http://data.chinabaogao.com/ \n",
    ">\n",
    "> read_html() 官网地址：[点击跳转](https://pandas.pydata.org/docs/reference/api/pandas.read_html.html?highlight=read_html)\n",
    ">\n",
    "> 网页中有表格元素（tbody）均可以下载  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1de61f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 举例说明，该 url 下有两个表格\n",
    "# 其他页面数据，只需修改该 url \n",
    "url = \"http://data.chinabaogao.com/dianxin/2021/11305620U2021.html\"\n",
    "\n",
    "result = pd.read_html(io=url,header=0)\n",
    "\n",
    "for d in result:\n",
    "    display(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51e04199",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13532cb2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bb9dc3a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0898578",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28a7d403",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85f8ba73",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "278.2px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
